const dbObj = require("./oprDb.js");

class User{
    constructor(){
        this.db = new dbObj();
    }
    endOfTheWork(){
        this.db.disConnectionDb();
    }

    async checkUserExist( account ){
        try{
            let res = await  this.db.queryData( "select cus_account from users where cus_account = '" + account + "';");
            return res;
        }catch(err){
            console.log("检查用户是否存在 错误: ", err);
        }
    }

    async registNewUserByEmail( account , password ){
        
        try{
            let res = await this.db.queryData("INSERT INTO users VALUES( 0, " +
               "'"+ account + "'," +
               "'"+ password + "'" + 
                ");   \
            ");

            
            return res;
        }catch(err){
            console.log("注册新用户， 错误： ", err);
        }
    }

    loginAccountByPassword( account ){
       
        return this.db.queryData("select cus_id, cus_passphrase ,cus_account from users where cus_account = '"+ account+"';" );
        
    }

    //改变用户登录状态
    changeUserStatus( data ){

        return this.db.queryData("call changeUserStatus( \
            '"+ data.userId + "', \
            '"+ data.status + "', \
            "+ this.db.getConn().escape( data.time )  + "\
        );");
        // this.db.queryData("INSERT INTO userStatuses(id , user_id , status , changeTime) \
        // VALUES ( \
        //     '"+ data.id + "', \
        //     '"+ data.userId + "', \
        //     '"+ data.status + "', \
        //     "+ this.db.getConn().escape( data.time )  + "\
        //     ); \
        // ");
    }
    //查看用户登录状态
    checkUserStatus( data ){
        return this.db.queryData(`
            select status from userStatuses where user_id = ${data.userId}
        `);
    }

    //查询用户顶级目录
    getTopdirOfUser( userId , gdata ){
        return this.db.queryData(`
        select SQL_CALC_FOUND_ROWS  
        top_dir_id as top_id ,
        json_value( dirData , '$.title' )  as title , 
        json_value( dirData , '$.common' )  as common
        from topDir 
        where userid = ${userId}
        order by top_dir_id
        limit ${gdata.offset} , ${gdata.pageSize};
        SELECT FOUND_ROWS() as total;
        `)
        // .batch(`
        // select dirData from topDir
        // where userid = ${userId};
        // `);
    }
    //更新目录结构 添加新笔记本
    updataTopDirOfUser( data ){

        return this.db.queryData(`
            insert into topDir( top_dir_id , userid , dirData )
            values(0 ,
                ${data.userId},
                ${ this.db.getConn().escape( data.topDirContent )}
                );
        `); //${data.topDirContent}
        // return this.db.queryData(`
        //     set @hasBeen = 0;
        //     select count(*) into @hasBeen from topDir where userid = ${data.userId};
        //     if @hasBeen = 0 then
        //     insert into topDir( top_dir_id , userid , dirData )
        //     values(0 ,
        //         ${data.userId},
        //         ${ this.db.getConn().escape( data.topDirContent )}
        //         );
        //     else
        //     update topDir
        //     set dirData = ${ this.db.getConn().escape( data.topDirContent )}
        //     where userid = ${data.userId};
        //     end if;
        // `);
        
        
    }

    //添加子目录
    addInnerDirOfUser( innerDirData ){
        if( innerDirData.top_id ){
            //在顶级目录下创建
            return this.db.queryData(`
            insert into innerDir( innerDir_id , topDir_id , subDir_id , innerDir_data)
            values(0,
                ${innerDirData.top_id},
                null,
                ${ this.db.getConn().escape( innerDirData.content ) }
                );
            `);
        }
        if( innerDirData.inner_id ){
            //在次级目录下创建
            return this.db.queryData(`
            insert into innerDir( innerDir_id , topDir_id , subDir_id , innerDir_data)
            values(
                0,
                null,
                ${innerDirData.inner_id},
                ${ this.db.getConn().escape( innerDirData.content ) }
            );            
            `);
        }
    }
    //获取子目录
    getInnerDirOfCurrentDir( currentItem ){
        
        //确定当前目录是谁的，并寻找该目录的下一级目录
        //顶级目录下
        if( currentItem.top_id ){
            // return this.db.queryData(`
            // select SQL_CALC_FOUND_ROWS
            // innerDir_id as inner_id,
            // json_value( innerDir_data , '$.title' )  as title , 
            // json_value( innerDir_data , '$.common' )  as common
            // from innerDir
            // where topDir_id = ${currentItem.top_id}
            // order by innerDir_id
            // limit ${currentItem.offset} , ${currentItem.pageSize};
            // SELECT FOUND_ROWS() as total;
            // `);
            /*
            select 
            SQL_CALC_FOUND_ROWS
            innerDir_id as inner_id ,
            json_value( innerDir_data , '$.title' )  as title , 
            json_value( innerDir_data , '$.common' )  as common,
            from innerDir
            where topDir_id = ${currentItem.top_id}
            order by innerDir_id
            limit ${currentItem.offset} , ${currentItem.pageSize};
            SELECT FOUND_ROWS() as total ;
            */
            return this.db.queryData(`
            

            select
            SQL_CALC_FOUND_ROWS
            t2.innerDir_id as inner_id ,
            cast( '' as char ) as art_id, 
            json_value( t2.innerDir_data , '$.title' )  as title,
            json_value( t2.innerDir_data , '$.common' )  as common,
            cast( 0 as int ) as type
            from topDir as t1
            inner join innerDir as t2  on t1.top_dir_id = t2.topDir_id 
            where t1.top_dir_id = ${ currentItem.top_id } 
            union 
            select 
            cast( '' as char ) as inner_id,
            t3.article_id as art_id ,
            t3.art_title as title,
            cast( '' as char ) as common,
            cast( 1 as int ) as type
            from topDir as t4
            inner join articles as t3 on  t4.top_dir_id = t3.topDir_id 
            where t4.top_dir_id = ${ currentItem.top_id }
            limit  ${currentItem.offset} , ${currentItem.pageSize};
            SELECT FOUND_ROWS() as total ;
            
            `);
        }
        //次级目录下
        if( currentItem.inner_id ){
            
            return this.db.queryData(`
           
            
           select
           SQL_CALC_FOUND_ROWS
           innerDir_id as inner_id ,
           cast( '' as char ) as art_id, 
           json_value( innerDir_data , '$.title' )  as title,
           json_value( innerDir_data , '$.common' )  as common,
           cast( 0 as int ) as type
           from innerDir 
           where subDir_id = ${ currentItem.inner_id } 
           union 
           select 
           cast( '' as char ) as inner_id,
           article_id as art_id ,
           art_title as title ,
           cast( '' as char ) as common ,
           cast( 1 as int ) as type
           from articles 
           where innerDir_id = ${ currentItem.inner_id }
           limit  ${currentItem.offset} , ${currentItem.pageSize};
           SELECT FOUND_ROWS() as total ;

            `);
        //     select
        //    SQL_CALC_FOUND_ROWS
        //    innerDir_id as inner_id ,
        //    cast( '' as char ) as art_id, 
        //    json_value( innerDir_data , '$.title' )  as title,
        //    json_value( innerDir_data , '$.common' )  as common,
        //    cast( 0 as int ) as type
        //    from innerDir 
        //    where subDir_id = ${ currentItem.inner_id } 
        //    union 
        //    select 
        //    cast( '' as char ) as inner_id,
        //    t2.article_id as art_id ,
        //    t2.art_title as title ,
        //    cast( '' as char ) as common ,
        //    cast( 1 as int ) as type
        //    from innerDir as t1
        //    inner join articles as t2 on  t1.innerDir_id = t2.innerDir_id
        //    where t2.innerDir_id = ${ currentItem.inner_id }
        //    limit  ${currentItem.offset} , ${currentItem.pageSize};
        //    SELECT FOUND_ROWS() as total ;

         /*
            select SQL_CALC_FOUND_ROWS
            innerDir_id as inner_id,
            json_value( innerDir_data , '$.title' )  as title , 
            json_value( innerDir_data , '$.common' )  as common
            from innerDir
            where subDir_id = ${currentItem.inner_id}
            order by innerDir_id
            limit ${currentItem.offset} , ${currentItem.pageSize};
            SELECT FOUND_ROWS() as total;
            */
        }
        
    }
    //删除目录
    removeDir( target ){
        //需要思考事务帮助数据的准确删除 留下以后完善
        if( target.top_id ){
            //删除顶层目录
            // select t1.top_dir_id , t1.userid , t2.innerDir_id
            // return this.db.queryData(`
            // delete  t2 , t1
            // from
            //     topDir as t1  
            //     right join   innerDir as t2 on  t2.topDir_id = t1.top_dir_id
                
            // where
            //     t1.top_dir_id = ${target.top_id};
            
            // `);
            // right join   innerDir as t3 on  t2.subDir_id  = t3.innerDir_id
            // return this.db.queryData(`
            
            // `);

            return this.db.queryData(`
            
            set @pathStr = '';
            set @len = 0;
            select deleDir( ${target.top_id} ) into @pathStr ;
            
            select func_get_split_string_total( @pathStr , ',' )  into  @len;
            
            while @len > 0 do
            
            set @index = 0;
            select func_get_split_string( @pathStr , ',' , @len ) into @index;
            
            #delete articles
            delete from articles where innerDir_id = @index;

            #delete menus
            delete from innerDir where innerDir_id = @index ; 

            set @len = @len - 1;
            end while;

            delete from articles where topDir_id = ${target.top_id};
            delete from topDir where top_dir_id = ${target.top_id};
            `);
           

        }
        if( target.inner_id ){
            console.log( "delete inner dir: ", target.inner_id);
            //删除次级目录
            return this.db.queryData(`
            set @innerPath = '';
            select deleInnerDir( ${target.inner_id} ) into @innerPath;
            
            
            set @len = 0;
            select func_get_split_string_total( @innerPath , ',' )  into  @len;
            
            while @len > 0 do
            set @index = 0;
            select func_get_split_string( @innerPath , ',' , @len ) into @index;

            #delete articles
            delete from articles where innerDir_id = @index;

            #delete menus
            delete from innerDir where innerDir_id = @index;  

            set @len = @len - 1;
            end while;

            `);
            
        }

        if( target.inner_id == '' && target.art_id ){
            return this.rmArticle( target );
        }
    }

    //删除文章(未)
    rmArticle( target ){
        if( target.art_id ){
           return  this.db.queryData(`
            delete from articles
            where article_id = ${ target.art_id };
            `);
        }
    }

    //新建文章
    createArticle( target ){
        if( target.top_id ){
            //在顶层目录创建文章
            return this.db.queryData(`
            insert into articles(
                article_id,
                art_title,
                art_content,
                create_time,
                updata_time,
                innerDir_id,
                topDir_id
            )
            values(
                0,
                '${ target.title }',
                '',
                ${this.db.getConn().escape( new Date() )},
                null,
                null,
                ${ target.top_id }
            );
            `);
        }
        if( target.inner_id ){
            //在次级目录创建文章
            return this.db.queryData(`
            insert into articles(
                article_id,
                art_title,
                art_content,
                create_time,
                updata_time,
                innerDir_id,
                topDir_id
            )
            values(
                0,
                '${ target.title }',
                '',
                ${this.db.getConn().escape( new Date() )},
                null,
                ${ target.inner_id },
                null
            );
            `);
        }
    }

    //目录重命名(未)
    reNameOfDirOrArt( target ){
        //重命名顶级目录
        if( target.top_id ){
            //重命名顶层目录
            return this.db.queryData(`
            update topDir
            set
            dirData = ${
                this.db.getConn().escape({
                    title: target.content.title,
                    common: target.content.common
                })
            }
            where top_dir_id = ${ target.top_id };
            `);
        }
        //重命名子目录
        if( target.inner_id ){
            //重命名次级目录
            return this.db.queryData(`
            update innerDir
            set 
            innerDir_data = ${ this.db.getConn().escape( target.content ) }
            where innerDir_id = ${ target.inner_id };
            `);
        }
        //重命名文章
        if( target.art_id ){
            return this.db.queryData(`
            update articles
            set 
            art_title = '${ target.content.title }',
            updata_time = ${ this.db.getConn().escape( new Date() ) }
            where article_id = ${ target.art_id };
            `);
        }

    }

    //文章编辑
    changeArtContent( target ){
        return this.db.queryData(`
        update  articles
        set art_content = ${ this.db.getConn().escape(target.content) }
        where article_id = ${ target.art_id };
        `);
    }

    //获取文章内容
    getArticleContent( target ){
        return this.db.queryData(`
        select art_content from articles
        where article_id = ${ target.art_id };
        `);
    }

    //获取用户信息
    getUserInformation( user_id ){
        return this.db.queryData(`
        insert into userInfo( id , user_id , avatar , nick_name )
        select 0 , ${ user_id } , null , null from dual
        where not exists( select * from  userInfo where user_id = ${ user_id } );

        select avatar as headerImg , nick_name as epithet from userInfo where user_id = ${ user_id };
        `);
    }

    //设置头像
    setUserAvatar( user_id , face ){
        return this.db.queryData(`
        update userInfo
        set avatar = ${ this.db.getConn().escape( face ) }
        where user_id = ${ user_id };
        `);
    }

    //设置昵称
    setUserNickName( user_id , nickName ){
        return this.db.queryData(`
        update userInfo
        set 
        nick_name = '${ nickName }'
        where user_id = ${ user_id };
        `);
    }

    //获取用户密码
    getUserPwd( user_id ){
        return this.db.queryData(`
        select cus_passphrase as pwd from users where cus_id = ${ user_id };
        `);
    }

    //改变用户密码
    changeUserPwd( user_id , newPwd ){
        return this.db.queryData(`
        update users 
        set cus_passphrase = '${newPwd}'
        where cus_id = ${ user_id };
        `);
    }

    //安全退出
    saftyLogout( user_id ){
        return this.db.queryData(`
        update userStatuses
        set 
        status = 1,
        changeTime = ${ this.db.getConn().escape( new Date() ) }
        where user_id = ${ user_id };
        `);
    }

}

//单例
let ProxyCreatUser = (function(){
    process.instanceUser = null;
    return function(){
        if(process.instanceUser){
            return process.instanceUser
        }
        return process.instanceUser = new User();
    }
})();

// exports.User = User;
module.exports = ProxyCreatUser();